﻿// -----------------------------------------------------------------------
// <copyright file="CompositionProfileListData.cs" company="VDC-IT" author="LTDUC">
//      Copyright (c) VDC-IT. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

namespace VDC.VONE.DAL
{
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Reflection;
    using Microsoft.ApplicationBlocks.Data;
    using VDC.COMMON;
    using VDC.VONE.ENTITIES;
    
    /// <summary>
    /// Class access to db CompositionProfileList
    /// </summary>
    public class CompositionProfileListData
    {
        /// <summary>
        /// Get CompositionProfileList By Id
        /// </summary>
        /// <param name="compositionProfileListId">Param compositionProfileListId</param>
        /// <returns>Return CompositionProfileList</returns>
        public CompositionProfileListEN GetCompositionProfileListById(int compositionProfileListId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                CompositionProfileListEN compositionProfileList = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCompositionProfileListGetById, compositionProfileListId);
                while (reader.Read())
                {
                    compositionProfileList = new CompositionProfileListEN();

                    if (!reader.IsDBNull(0))
                    {
                        compositionProfileList.CompositionProfileListId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        compositionProfileList.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        compositionProfileList.Name = reader.GetString(2);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return compositionProfileList;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get CompositionProfileList By Id
        /// </summary>
        /// <param name="name">Param name</param>
        /// <returns>Return CompositionProfileList</returns>
        public CompositionProfileListEN GetCompositionProfileListByName(string name)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                CompositionProfileListEN compositionProfileList = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCompositionProfileListGetByName, name);
                while (reader.Read())
                {
                    compositionProfileList = new CompositionProfileListEN();

                    if (!reader.IsDBNull(0))
                    {
                        compositionProfileList.CompositionProfileListId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        compositionProfileList.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        compositionProfileList.Name = reader.GetString(2);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return compositionProfileList;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list all CompositionProfileList
        /// </summary>
        /// <returns>Return list all CompositionProfileList</returns>
        public IList<CompositionProfileListEN> GetCompositionProfileLists()
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<CompositionProfileListEN> lstCompositionProfileList = new List<CompositionProfileListEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCompositionProfileListGetAll);
                while (reader.Read())
                {
                    CompositionProfileListEN compositionProfileList = new CompositionProfileListEN();

                    if (!reader.IsDBNull(0))
                    {
                        compositionProfileList.CompositionProfileListId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        compositionProfileList.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        compositionProfileList.Name = reader.GetString(2);
                    }

                    lstCompositionProfileList.Add(compositionProfileList);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstCompositionProfileList;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Insert to db CompositionProfileList
        /// </summary>
        /// <param name="compositionProfileList">Paarm CompositionProfileList</param>
        /// <returns>Insert CompositionProfileList</returns>
        public int Insert(CompositionProfileListEN compositionProfileList)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.CompositionProfileList.Code, compositionProfileList.Code),
                                    new SqlParameter(Constants.SqlParamerterName.CompositionProfileList.Name, compositionProfileList.Name)
                                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCompositionProfileListInsert, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Update to db CompositionProfileList
        /// </summary>
        /// <param name="compositionProfileList">Param CompositionProfileList</param>
        public void Update(CompositionProfileListEN compositionProfileList)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.CompositionProfileList.CompositionProfileListId, compositionProfileList.CompositionProfileListId),
                                    new SqlParameter(Constants.SqlParamerterName.CompositionProfileList.Code, compositionProfileList.Code),
                                    new SqlParameter(Constants.SqlParamerterName.CompositionProfileList.Name, compositionProfileList.Name)
                                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCompositionProfileListUpdate, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Delete CompositionProfileList by compositionProfileListId
        /// </summary>
        /// <param name="compositionProfileListId">param compositionProfileListId</param>
        public void Delete(int compositionProfileListId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCompositionProfileListDelete, compositionProfileListId);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Exists the specified composition profile list id.
        /// </summary>
        /// <param name="compositionProfileListId">The composition profile list id.</param>
        /// <param name="name">The name.</param>
        /// <returns>return Exits</returns>
        /// <exception cref="System.Exception">SqlException ex</exception>
        public bool Exist(int compositionProfileListId, string name)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                    {
                        new SqlParameter(Constants.SqlParamerterName.CompositionProfileList.CompositionProfileListId, compositionProfileListId),
                        new SqlParameter(Constants.SqlParamerterName.CompositionProfileList.Name, name)
                    };

                var result =
                    Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCompositionProfileListExist, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result > 0;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list all CompositionProfileList By Name
        /// </summary>
        /// <param name="profileTypeId">param profileTypeId</param>
        /// <param name="name">param name</param>
        /// <returns>Return list all CompositionProfileList</returns>
        public IList<CompositionProfileListEN> GetCompositionProfileListSearch(int profileTypeId, string name)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<CompositionProfileListEN> lstCompositionProfileList = new List<CompositionProfileListEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCompositionProfileListSearch, profileTypeId, !string.IsNullOrWhiteSpace(name) ? name.Trim() : (object)DBNull.Value);
                while (reader.Read())
                {
                    CompositionProfileListEN compositionProfileList = new CompositionProfileListEN();

                    if (!reader.IsDBNull(0))
                    {
                        compositionProfileList.CompositionProfileListId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        compositionProfileList.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        compositionProfileList.Name = reader.GetString(2);
                    }

                    lstCompositionProfileList.Add(compositionProfileList);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstCompositionProfileList;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list all CompositionProfileList By Name
        /// </summary>
        /// <param name="profileTypeId">param profileTypeId</param>
        /// <returns>Return list all CompositionProfileList</returns>
        public IList<CompositionProfileListEN> GetCompositionProfileListByProfileTypeId(int profileTypeId, string name)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<CompositionProfileListEN> lstCompositionProfileList = new List<CompositionProfileListEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCompositionProfileListGetByProfileTypeId, profileTypeId, name);
                while (reader.Read())
                {
                    CompositionProfileListEN compositionProfileList = new CompositionProfileListEN();

                    if (!reader.IsDBNull(0))
                    {
                        compositionProfileList.CompositionProfileListId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        compositionProfileList.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        compositionProfileList.Name = reader.GetString(2);
                    }

                    lstCompositionProfileList.Add(compositionProfileList);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstCompositionProfileList;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }
    }
}